package com.xxtx.trade.common.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.stereotype.Component;

@Component
public class ExcelUtils {
    /**
     * 创建列标题栏样式
     * 水平垂直居中
     */
    public static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontSize) {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints(fontSize);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 创建新工作表  （管理员导出全部excel）
     */
    public static HSSFSheet createWorkbook(HSSFWorkbook workbook, HSSFCellStyle cellStyle, int num) {
        HSSFSheet sheet = workbook.createSheet("明细"+num);
        sheet.setDefaultColumnWidth(18);
        String [] titles = {"ICCID号","接入号","供应商名称","所属代理商","代理商层级","导入批次","激活时间","到期时间","总流量（M）","已使用流量（M）","剩余流量（M）","卡类型","卡状态","备注"};
        HSSFRow row = sheet.createRow(0);
        for(int i=0; i<titles.length; i++){
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(titles[i]);
        }
        return sheet;
    }
    /**
     * 创建新工作表  （管理员导出全部excel）
     */
    public static HSSFSheet createAgentSimInfoWorkbook(HSSFWorkbook workbook, HSSFCellStyle cellStyle, int num) {
        HSSFSheet sheet = workbook.createSheet("明细"+num);
        sheet.setDefaultColumnWidth(18);
        String [] titles = {"ICCID号","接入号","所属代理商","激活时间","到期时间","总流量（M）","已使用流量（M）","剩余流量（M）","卡类型","卡状态","备注"};
        HSSFRow row = sheet.createRow(0);
        for(int i=0; i<titles.length; i++){
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(titles[i]);
        }
        return sheet;
    }
    /**
     * 创建新工作表  （管理员导出全部excel）
     */
    public static HSSFSheet adminExpireExportExcel(HSSFWorkbook workbook, HSSFCellStyle cellStyle, int num) {
        HSSFSheet sheet = workbook.createSheet("明细"+num);
        sheet.setDefaultColumnWidth(18);
        String [] titles = {"ICCID号","接入号","剩余天数","供应商名称","所属代理商","代理商层级","导入批次","激活时间","到期时间","总流量（M）","已使用流量（M）","剩余流量（M）","卡状态"};
        HSSFRow row = sheet.createRow(0);
        for(int i=0; i<titles.length; i++){
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(titles[i]);
        }
        return sheet;
    }
    /**
     * 创建新工作表  （管理员导出全部excel）
     */
    public static HSSFSheet agentExpireExportExcel(HSSFWorkbook workbook, HSSFCellStyle cellStyle, int num) {
        HSSFSheet sheet = workbook.createSheet("明细"+num);
        sheet.setDefaultColumnWidth(18);
        String [] titles = {"ICCID号","接入号","剩余天数","所属代理商","激活时间","到期时间","总流量（M）","已使用流量（M）","剩余流量（M）","卡状态"};
        HSSFRow row = sheet.createRow(0);
        for(int i=0; i<titles.length; i++){
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(titles[i]);
        }
        return sheet;
    }

    public static HSSFSheet supplierSimTimerList(HSSFWorkbook workbook, HSSFCellStyle cellStyle, int num) {
        HSSFSheet sheet = workbook.createSheet("明细"+num);
        sheet.setDefaultColumnWidth(18);
        String [] titles = {"ICCID号","运营商名称","备注","时间"};
        HSSFRow row = sheet.createRow(0);
        for(int i=0; i<titles.length; i++){
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(titles[i]);
        }
        return sheet;
    }
}
